#-*- codeing = utf-8 -*-
#@Time : 2020/8/4 23:40
#@Author : 媛媛
#@File : 升级版.py
#@Software: PyCharm

from bs4 import BeautifulSoup
import urllib.request,urllib.error
import xlwt
import requests
# from selenium import webdriver
import re
import sqlite3
# import MySQLdb
# import MySQLdb.cursors

findsubject = re.compile(r'"subject":"(.*?)","content":', re.S)
findcontent = re.compile(r'"content":"(.*?)","dateline":', re.S)
findtypeName = re.compile(r'"typeName":"(.*?)","domainName":', re.S)
finddomainName = re.compile(r'"domainName":"(.*?)","sourceName":', re.S)
findyear = re.compile(r'"year":"(\d*)","content":', re.S)
MYSQL_HOST = 'localhost'
MYSQL_DBNAME ='python'
MYSQL_USER = 'root'
MYSQL_PASSWORD = 'cmh0226'



def main():
    url = 'http://liuyan.people.com.cn/threads/queryThreadsList'
    dataurl = askurl(url)
    # datalist = getData(dataurl)
    # dbfile = 'shengjiban.db'
    # sqldb(dbfile)

    # saveDB(datalist, dbfile)



def askurl(url):
    # driver = webdriver.Chrome()
    # Cookie = driver.get('http://liuyan.people.com.cn/threads/list?fid=539')
    head = {
        'Accept': 'application/json, text/javascript, */*; q=0.01',
        # 'Accept-Encoding': 'gzip, deflate',
        # 'Accept-Language': 'zh-CN,zh;q=0.9',
        'Connection': 'keep-alive',
        'Content-Length': '24',
        'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8',
        'Cookie': 'aliyungf_tc=AQAAAB1TaSZSew0AwzVAcADdJxjvghXI; 4de1d0bdb25d4625be2481a1b9e1350f=WyIyNjEwMDAwNTk5Il0; wdcid=2583a06ba08186a6; wdses=00d3784125b03cf7; sso_s=46895873d4624e408ce849cf1c35fbb9PFrrY4zn; sso_u=A+v/tgb8AqanbgaYbq6EAqc+bNDnZJB4Z12EZ9RQZ4R3p9pX; sso_l=bgaYbq6EAqc+bND_; u_i=JpkcQzxilmIlj3Mst5x+kQ==; u_n_i=5aSp57q15Li25pif6L6wCTA=; sso_d=1596512029074; rand_code=Dggqo1HTM7pIFMRXvrKSpA==; wdlast=1596512045; JSESSIONID=B7D03953BEB484728F498F6C3015213E',
        'Host': 'liuyan.people.com.cn',
        'Origin': 'http://liuyan.people.com.cn',
        'Referer': 'http://liuyan.people.com.cn/threads/list?fid=539',
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.106 Safari/537.36',
        'X-Requested-With': 'XMLHttpRequest',
    }
    list = []
    for i in range(0, 10):
        data = {
            'fid': 539+i,
            'lastItem': '0',
        }
        request = requests.post(url,  data=data, headers=head)
        # print(request)
        request = request.text
        list.append(request)
    # print(list)
    return list

def getData(dataurl):
    dataurl = str(dataurl)
    print(dataurl)
    datalist = []
    for i in range(0, 100):
        data = []
        subject = re.findall(findsubject, dataurl)[i]
        data.append(subject)
        # print(subject)
        content = re.findall(findcontent, dataurl)[i]
        data.append(content)
        # print(content)
        typeName = re.findall(findtypeName, dataurl)[i]
        data.append(typeName)
        # print(typeName)
        domainName = re.findall(finddomainName, dataurl)[i]
        data.append(domainName)
        datalist.append(data)
        # print(domainName)
        # year = re.findall(findyear,dataurl)
    print(datalist)

    return datalist

def saveDB(datalist, dbfile):
    # sqldb(dbfile)
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()
    for data in datalist:
        for index in range(len(data)):
            if index == 4 or index == 5:
                continue
            data[index] = '"' + data[index] + '"'
        sql = '''
                insert into shengjiban (
                subject,content,typeName,datalist) 
                values(%s)''' % ",".join(data)
        print(sql)
        cur.execute(sql)
        conn.commit()
    cur.close()
    conn.close()



def sqldb(dbfile):
    sql = '''
    create table shengjiban 
    (
    id integer primary key autoincrement,
    subject text,
    content text,
    typeName varchar,
    datalist varchar 
    )
    '''
    conn = sqlite3.connect(dbfile)
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
    conn.close()



if __name__ == '__main__':
    main()